package com.wutongyu.mannyburger;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

public class ProductDatabaseHelper extends SQLiteOpenHelper {

    private static final String TAG = "ProductDatabaseHelper";
    private static final String DATABASE_NAME = "mannyburger.db";
    private static final int DATABASE_VERSION = 2; // 升级数据库版本
    private static final String TABLE_PRODUCTS = "products";

    public ProductDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public static String getTableProducts() {
        return TABLE_PRODUCTS;
    }

    // 表结构
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_TABLE = "CREATE TABLE " + TABLE_PRODUCTS + "(" +
                "pid INTEGER PRIMARY KEY AUTOINCREMENT," +
                "pname TEXT NOT NULL," +
                "shop_price REAL NOT NULL," +
                "description TEXT," +
                "category TEXT," +
                "image_url TEXT," +
                "image_res_id INTEGER DEFAULT 0" +
                ")";
        db.execSQL(CREATE_TABLE);

        insertInitialData(db);
    }

    // 样例数据，在进入应用时插入数据库中
    private void insertInitialData(SQLiteDatabase db) {
        // 汉堡类
        insertProduct(db, "经典牛肉汉堡", 20.0, "100%纯牛肉，搭配新鲜生菜、番茄和特制酱料", "汉堡", R.drawable.burger_classic);
        insertProduct(db, "芝士培根汉堡", 25.0, "香浓芝士，搭配烟熏培根和特制酱料", "汉堡", R.drawable.burger_cheese);
        insertProduct(db, "鸡肉汉堡", 18.0, "酥脆鸡排，搭配新鲜生菜和特制酱料", "汉堡", R.drawable.burger_chicken);
        insertProduct(db, "墨西哥风味汉堡", 23.0, "牛肉饼配墨西哥辣椒、芝士和莎莎酱", "汉堡", R.drawable.burger_mexican);
        insertProduct(db, "素食汉堡", 16.0, "精选蔬菜制成的素食饼，健康美味", "汉堡", R.drawable.burger_veggie);
        
        // 小吃类
        insertProduct(db, "香辣炸鸡腿", 16.0, "外酥里嫩，香辣可口", "小吃", R.drawable.snack_chiken);
        insertProduct(db, "薯条", 12.0, "酥脆金黄，外酥里嫩", "小吃", R.drawable.snack_fries);
        insertProduct(db, "洋葱圈", 10.0, "金黄酥脆，风味十足", "小吃", R.drawable.snack_onion_rings);
        insertProduct(db, "鸡米花", 15.0, "外酥里嫩，口感丰富", "小吃", R.drawable.snack_nuggets);
        
        // 饮料类
        insertProduct(db, "可乐", 8.0, "冰镇可乐，清爽解渴", "饮料", R.drawable.drink_cola);
        insertProduct(db, "雪碧", 8.0, "冰镇雪碧，清新怡人", "饮料", R.drawable.drink_sprite);
        insertProduct(db, "橙汁", 10.0, "鲜榨橙汁，营养健康", "饮料", R.drawable.drink_juice);
        
        // 套餐类
        insertProduct(db, "经典汉堡套餐", 35.0, "经典牛肉汉堡+薯条+可乐，超值套餐", "套餐", R.drawable.meal_classic);
        insertProduct(db, "芝士堡全家桶", 45.0, "芝士培根汉堡+香辣炸鸡腿+薯条+雪碧，丰盛美味", "套餐", R.drawable.meal_cheese);
        insertProduct(db, "超值双人餐", 65.0, "两个鸡肉汉堡+洋葱圈+薯条+两个橙汁，与好友共享", "套餐", R.drawable.meal_double);
    }
    
    private void insertProduct(SQLiteDatabase db, String name, double price, String description, String category, int imageResId) {
        ContentValues values = new ContentValues();
        values.put("pname", name);
        values.put("shop_price", price);
        values.put("description", description);
        values.put("category", category);
        values.put("image_res_id", imageResId);
        db.insert(TABLE_PRODUCTS, null, values);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
            // 添加新列
            try {
                db.execSQL("ALTER TABLE " + TABLE_PRODUCTS + " ADD COLUMN description TEXT");
                db.execSQL("ALTER TABLE " + TABLE_PRODUCTS + " ADD COLUMN category TEXT");
                db.execSQL("ALTER TABLE " + TABLE_PRODUCTS + " ADD COLUMN image_url TEXT");
                db.execSQL("ALTER TABLE " + TABLE_PRODUCTS + " ADD COLUMN image_res_id INTEGER DEFAULT 0");
                
                // 更新已有数据
                updateExistingData(db);
            } catch (Exception e) {
                Log.e(TAG, "数据库升级错误", e);
                // 如果升级失败，则重建表
                db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCTS);
                onCreate(db);
            }
        }
    }
    
    private void updateExistingData(SQLiteDatabase db) {
        // 更新汉堡数据
        updateProductCategory(db, "经典牛肉汉堡", "汉堡");
        updateProductCategory(db, "芝士培根汉堡", "汉堡");
        updateProductCategory(db, "鸡肉汉堡", "汉堡");
        updateProductCategory(db, "墨西哥风味汉堡", "汉堡");
        updateProductCategory(db, "素食汉堡", "汉堡");
        
        // 更新小吃数据
        updateProductCategory(db, "香辣炸鸡腿", "小吃");
        
        // 更新套餐数据
        updateProductCategory(db, "经典汉堡套餐", "套餐");
        updateProductCategory(db, "芝士堡全家桶", "套餐");
        updateProductCategory(db, "超值双人餐", "套餐");
    }
    
    private void updateProductCategory(SQLiteDatabase db, String productName, String category) {
        ContentValues values = new ContentValues();
        values.put("category", category);
        values.put("image_res_id", R.drawable.bg); // 默认使用背景图作为临时商品图
        
        db.update(TABLE_PRODUCTS, values, "pname = ?", new String[] { productName });
    }
    
    // 获取所有商品
    public List<Product> getAllProducts() {
        List<Product> products = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = null;
        try {
            cursor = db.query(TABLE_PRODUCTS, 
                    new String[]{"pid", "pname", "shop_price", "description", "category", "image_url", "image_res_id"}, 
                    null, null, null, null, null);
            
            if (cursor != null && cursor.moveToFirst()) {
                do {
                    int id = cursor.getInt(cursor.getColumnIndexOrThrow("pid"));
                    String name = cursor.getString(cursor.getColumnIndexOrThrow("pname"));
                    double price = cursor.getDouble(cursor.getColumnIndexOrThrow("shop_price"));
                    String description = cursor.getString(cursor.getColumnIndexOrThrow("description"));
                    String category = cursor.getString(cursor.getColumnIndexOrThrow("category"));
                    String imageUrl = cursor.getString(cursor.getColumnIndexOrThrow("image_url"));
                    int imageResId = cursor.getInt(cursor.getColumnIndexOrThrow("image_res_id"));
                    
                    Product product = new Product(id, name, price, description, category, imageResId);
                    product.setImageUrl(imageUrl);
                    products.add(product);
                } while (cursor.moveToNext());
            }
        } catch (Exception e) {
            Log.e(TAG, "获取产品列表时出错", e);
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        
        return products;
    }
    
    // 按分类获取商品
    public List<Product> getProductsByCategory(String category) {
        List<Product> products = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = null;
        try {
            cursor = db.query(TABLE_PRODUCTS, 
                    new String[]{"pid", "pname", "shop_price", "description", "category", "image_url", "image_res_id"}, 
                    "category = ?", new String[]{category}, null, null, null);
            
            if (cursor != null && cursor.moveToFirst()) {
                do {
                    int id = cursor.getInt(cursor.getColumnIndexOrThrow("pid"));
                    String name = cursor.getString(cursor.getColumnIndexOrThrow("pname"));
                    double price = cursor.getDouble(cursor.getColumnIndexOrThrow("shop_price"));
                    String description = cursor.getString(cursor.getColumnIndexOrThrow("description"));
                    String imageUrl = cursor.getString(cursor.getColumnIndexOrThrow("image_url"));
                    int imageResId = cursor.getInt(cursor.getColumnIndexOrThrow("image_res_id"));
                    
                    Product product = new Product(id, name, price, description, category, imageResId);
                    product.setImageUrl(imageUrl);
                    products.add(product);
                } while (cursor.moveToNext());
            }
        } catch (Exception e) {
            Log.e(TAG, "获取分类产品列表时出错", e);
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        
        return products;
    }
    
    // 通过ID获取单个商品
    public Product getProductById(int productId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Product product = null;
        
        Cursor cursor = null;
        try {
            cursor = db.query(TABLE_PRODUCTS, 
                    new String[]{"pid", "pname", "shop_price", "description", "category", "image_url", "image_res_id"}, 
                    "pid = ?", new String[]{String.valueOf(productId)}, null, null, null);
            
            if (cursor != null && cursor.moveToFirst()) {
                String name = cursor.getString(cursor.getColumnIndexOrThrow("pname"));
                double price = cursor.getDouble(cursor.getColumnIndexOrThrow("shop_price"));
                String description = cursor.getString(cursor.getColumnIndexOrThrow("description"));
                String category = cursor.getString(cursor.getColumnIndexOrThrow("category"));
                String imageUrl = cursor.getString(cursor.getColumnIndexOrThrow("image_url"));
                int imageResId = cursor.getInt(cursor.getColumnIndexOrThrow("image_res_id"));
                
                product = new Product(productId, name, price, description, category, imageResId);
                product.setImageUrl(imageUrl);
            }
        } catch (Exception e) {
            Log.e(TAG, "获取商品详情时出错", e);
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        
        return product;
    }
    
    // 获取相关推荐商品（同类别的其他商品）
    public List<Product> getRelatedProducts(int productId, String category, int limit) {
        List<Product> products = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = null;
        try {
            cursor = db.query(TABLE_PRODUCTS, 
                    new String[]{"pid", "pname", "shop_price", "description", "category", "image_url", "image_res_id"}, 
                    "category = ? AND pid != ?", 
                    new String[]{category, String.valueOf(productId)}, 
                    null, null, "RANDOM()", String.valueOf(limit));
            
            if (cursor != null && cursor.moveToFirst()) {
                do {
                    int id = cursor.getInt(cursor.getColumnIndexOrThrow("pid"));
                    String name = cursor.getString(cursor.getColumnIndexOrThrow("pname"));
                    double price = cursor.getDouble(cursor.getColumnIndexOrThrow("shop_price"));
                    String description = cursor.getString(cursor.getColumnIndexOrThrow("description"));
                    String imageUrl = cursor.getString(cursor.getColumnIndexOrThrow("image_url"));
                    int imageResId = cursor.getInt(cursor.getColumnIndexOrThrow("image_res_id"));
                    
                    Product product = new Product(id, name, price, description, category, imageResId);
                    product.setImageUrl(imageUrl);
                    products.add(product);
                } while (cursor.moveToNext());
            }
        } catch (Exception e) {
            Log.e(TAG, "获取相关商品列表时出错", e);
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        
        return products;
    }
}